package cnki.kg.demo.util;

import cnki.kg.demo.config.KbaseConfig;
import com.kbase.jdbc.ConnectionImpl;
import com.kbase.jdbc.StatementImpl;
import com.mysql.cj.jdbc.result.ResultSetImpl;
import kbase.KBaseClient;
import kbase.struct.TPI_RETURN_RESULT;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.ResourceUtils;

import java.io.File;
import java.sql.*;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.stream.Collectors;


@Component
public class KBaseUtils {
    private static final Logger log = LoggerFactory.getLogger(KBaseUtils.class);
    @Autowired
    private KbaseConfig kbaseConfig;
    public List<String> getKbaseviewList(){
        Connection conn = null;
        List<String> viewList=new ArrayList<>();
        try {
            Class.forName(kbaseConfig.getDriverclassname());
            conn = DriverManager.getConnection(kbaseConfig.getUrl(), kbaseConfig.getUsername(), kbaseConfig.getPassword());
            ConnectionImpl kbaseconnect = (ConnectionImpl) conn;
            KBaseClient client = kbaseconnect.getKbaseClient();
            int connectHSet = kbaseconnect.getConnectionHset();
           int viewCout= client.KBase_GetViewCount(connectHSet);
            for (int i = 0; i < viewCout; i++) {
                TPI_RETURN_RESULT viewResult = client.KBase_GetViewNameBySn(connectHSet,i);
                if(viewResult!=null){
                    String viewName=viewResult.rtnBuf;
                    if(StringUtil.isNotBlank(viewName)){
                        viewList.add(viewName);
                    }

                }
                System.out.println(viewList);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return viewList;
    }
    public HashMap<String, ArrayList<HashMap<String, String>>> getKbaseViewStruct(String view){
        Connection conn = null;
        StatementImpl stmt = null;
        com.kbase.jdbc.ResultSetImpl resultSetImpl = null;
        HashMap<String, ArrayList<HashMap<String, String>>> maps = new HashMap<>();
        try {
            Class.forName(kbaseConfig.getDriverclassname());
            conn = DriverManager.getConnection(kbaseConfig.getUrl(), kbaseConfig.getUsername(), kbaseConfig.getPassword());
            ConnectionImpl kbaseconnect = (ConnectionImpl) conn;
            KBaseClient client = kbaseconnect.getKbaseClient();
            int connectHSet = kbaseconnect.getConnectionHset();
            int viewCout= client.KBase_GetViewCount(connectHSet);
            stmt = (StatementImpl) conn.createStatement();
            for (int i = 0; i < viewCout; i++) {
                TPI_RETURN_RESULT viewResult = client.KBase_GetViewNameBySn(connectHSet,i);
                if(viewResult!=null){
                    String viewName=viewResult.rtnBuf;
                    if(StringUtil.isNotBlank(viewName)&&view.equals(viewName)){
                        //TPI_RETURN_RESULT viewItem= client.KBase_GetViewSQL(connectHSet,viewName,true);//乱码
                        TPI_RETURN_RESULT viewItem= client.KBase_GetViewSQL(connectHSet,viewName,false);
                        if(viewItem!=null){
                            String viewSql=viewItem.rtnBuf;
                            String sql = viewSql + " limit 1";
                            resultSetImpl = (com.kbase.jdbc.ResultSetImpl) stmt.executeQuery(sql);
                            TPI_RETURN_RESULT fieldNameResult = client.KBase_GetRecordSetFieldName(resultSetImpl.getResultSetImplhset(), 1);
                            //System.out.println("数据库："+dbName+"，表名："+tableNameList[k]+ ",字段:"+fieldNameResult.rtnBuf);
                            ArrayList<HashMap<String, String>> fieldMapList = new ArrayList<HashMap<String, String>>();
                            String[] fileNameArray = fieldNameResult.rtnBuf.split(",");
                            for (String field : fileNameArray) {
                                HashMap<String, String> fieldMap = new HashMap<String, String>();
                                String[] fieldItem = field.split(":");
                                fieldMap.put("ColumnName", fieldItem[0]);
                                if (fieldItem.length > 1) {
                                    fieldMap.put("AliaColumnName", fieldItem[1]);
                                } else {
                                    fieldMap.put("AliaColumnName", "");
                                }
                                int fieldIndex = client.KBase_GetFieldTypeByName(resultSetImpl.getResultSetImplhset(), fieldItem[0]);
                                TPI_RETURN_RESULT fieldType = client.KBase_GetFieldTypeNameBySN(fieldIndex);

                                if (fieldType.rtnBuf.length() > 10) {
                                    fieldMap.put("ColumnTypeName", "QSTRING");
                                } else {
                                    fieldMap.put("ColumnTypeName", fieldType.rtnBuf);
                                }
                                fieldMapList.add(fieldMap);
                            }
                            maps.put(viewName, fieldMapList);
                            break;
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return maps;
    }
    private List<String> getViewFieldBySql(String sql) {
        List<String> result = new ArrayList<>();
        try {
            sql = sql.toLowerCase();
            String field = sql.substring(7, sql.indexOf("from") - 1);
            String[] fields = field.split(",");
            for (int i = 0; i <= fields.length; i++) {
                String name = fields[i].trim();
                if(StringUtil.isNotBlank(name)){
                    if (name.contains(" as ")) {
                        name = name.substring(name.lastIndexOf("as") + 2).trim();
                        result.add(name);
                    }else{
                        result.add(name);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e.getMessage() + e.getStackTrace());
        }
        if(result!=null&&result.size()>0){
            result=result.stream().distinct().collect(Collectors.toList());
        }
        return result;
    }
    public Integer excuteSalar(String sql) throws SQLException {
        Integer result = null;
        ResultSet rs = null;
        Connection conn = null;
        Statement pst = null;
        try {
            Class.forName(kbaseConfig.getDriverclassname());
            conn = DriverManager.getConnection(kbaseConfig.getUrl(), kbaseConfig.getUsername(), kbaseConfig.getPassword());
            pst = conn.createStatement();
            log.info(sql);
            rs = pst.executeQuery(sql);
            rs.next();
            result = rs.getString(1)!=null?Integer.parseInt(rs.getString(1)):0;
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e.getMessage() + e.getStackTrace());
        } finally {
            rs.close();
            conn.close();
        }

        return result;
    }
    public List<HashMap<String, String>> excuteQuery(String sql) {
        List<HashMap<String, String>> result = new ArrayList<>();
        try {
            ResultSetImpl krs = null;
            Connection conn = null;
            Statement pst = null;
            Class.forName(kbaseConfig.getDriverclassname());
            conn = DriverManager.getConnection(kbaseConfig.getUrl(), kbaseConfig.getUsername(), kbaseConfig.getPassword());
            pst = conn.createStatement();
            ResultSet rs = pst.executeQuery(sql);
            ResultSetMetaData md = rs.getMetaData(); // 得到结果集的结构信息，比如字段数、字段名等
            int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数
            while (rs.next()) {
                HashMap<String, String> map = new HashMap<String, String>(columnCount);
                for (int i = 1; i <= columnCount; i++) {
                    String cloumnName = "";
                    cloumnName = md.getColumnName(i);
                    String value = "";
                    if (rs.getString(i) != null) {
                        value = String.valueOf(rs.getString(i));// 获取字段值
                    }
                    map.put(cloumnName, value);
                }
                result.add(map);
            }
            rs.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public boolean excuteSql(String sql) {
        Connection conn = null;
        Statement pst = null;
        boolean result = false;
        try {
            Class.forName(kbaseConfig.getDriverclassname());
            conn = DriverManager.getConnection(kbaseConfig.getUrl(), kbaseConfig.getUsername(), kbaseConfig.getPassword());
            pst = conn.createStatement();
            result = pst.execute(sql);
            pst.close();
            conn.close();
        } catch (Exception e) {
            log.error(e.getMessage() + e.getStackTrace());
            e.printStackTrace();
        }
        return result;
    }
    public boolean InsertByRec(String sysid, String tableName, String content, boolean isUnicode) throws Exception {
        boolean result = false;
        Connection conn = null;
        try {
            //获得KBase客户端
            Class.forName(kbaseConfig.getDriverclassname());
            conn = DriverManager.getConnection(kbaseConfig.getUrl(), kbaseConfig.getUsername(), kbaseConfig.getPassword());
            ConnectionImpl kbaseconnect = (ConnectionImpl)conn;
            KBaseClient client = kbaseconnect.getKbaseClient();
            int connectHSet = kbaseconnect.getConnectionHset();
            //获得KBaseServer地址
            TPI_RETURN_RESULT kbaseResult = client.KBase_GetServerPath(connectHSet);
            if (kbaseResult.iResult >= 0) {
                String serverPath = kbaseResult.rtnBuf;
                // 获取跟目录
                File path;
                path = new File(ResourceUtils.getURL("classpath:").getPath());
                if (!path.exists())
                    path = new File("");

                // 如果上传目录为/static/rec/，则可以如下获取：
                File file = new File(path.getAbsolutePath(), "static/rec/");
                if (!file.exists())
                    file.mkdirs();
                log.trace("file url:" + file.getAbsolutePath());

                String fileName = tableName + "_" + sysid + ".txt";
                String srcFile = file.getAbsolutePath() + "\\" + fileName;
                String targetFile = serverPath + "/" + fileName;
                log.trace("srcFile:{} targetFile:{}", srcFile, targetFile);
                String sql = "BULKLOAD TABLE {0} ''{1}''";

                // 生成文件
                boolean isWrite = FileHelper.writeTxtFile(content, srcFile, isUnicode);
                if (isWrite) {
                    int f = client.KBase_WriteFile(connectHSet, srcFile, targetFile);
                    // 更新数据
                    result = excuteNoQuery(MessageFormat.format(sql, tableName, targetFile));
                    log.trace("writeFile:{}  执行结果:{}", f, result);
                }
                // 删除临时文件
                FileHelper.deleteTxtFile(srcFile);
                client.KBase_DeleteFiles(connectHSet, targetFile);
            }
        } catch (Exception e) {
            log.error(e.toString());
        } finally {
            conn.close();
        }
        return result;
    }
    public boolean excuteNoQuery(String sql) throws SQLException {
        boolean rs = false;
        Connection conn = null;
        Statement pst = null;
        try {
            // 从连接池 中 取得 连接
            Class.forName(kbaseConfig.getDriverclassname());
            conn = DriverManager.getConnection(kbaseConfig.getUrl(), kbaseConfig.getUsername(), kbaseConfig.getPassword());
            PreparedStatement stmt = conn.prepareStatement(sql);
            // 执行
            rs = stmt.execute(sql);
        } catch (Exception e) {
            log.error(e.toString());
        } finally {
            conn.close();
        }
        return rs;
    }
}
